import sys

import tushare as ts
import  pandas as pd
#数据库依赖包 安装用命令pip install  pymysql /pip install MySQLdb（Python2）
import pymysql
import requests
import time
#时间格式化方法
import datetime
from sqlalchemy import create_engine
import re

#设置的全局变量
index=0
firstData=''
secondData=''
base_res= '序号,预售证号,项目名称,开发商,所在区,批准时间,位置,受让日期,区域,使用年限,房屋用途,预售数量,总建筑面积\n'
desc_res='预售证号,项目名称,楼栋,售价,楼层,房号,用途,建筑面积,户内面积,分摊面积\n'
desc_index=0
houseList=[]
certList=[]
def getFirstData():
    print('获取首页数据','getFirstData')
    global  firstData
    #headers={'Content-Type': 'application/json'}
    #r0 = requests.get('http://zjj.sz.gov.cn/ris/bol/szfdc/index.aspx')
    headers = {
        'Content-Type': 'application/x-www-form-urlencoded'
    }
    mybody='scriptManager2=updatepanel2|AspNetPager1&__EVENTTARGET=AspNetPager1&__EVENTARGUMENT=@num@&__LASTFOCUS=&__VIEWSTATE=CRLk4GeAnUzjmsIrkr1AQSMf9%2B%2B0VHmINHEqQMsRQOz7Jiv4Ww22eiEgP8AnNthVvjlKwHrCzhlHK2NeJX4OlqCqto1Jj%2Bl9imgVCqUA31wGaC6NoLblcCDFggQj8Pph1ncaOTuCjOThyRUScD3Rn9GBGiLk%2BvAAav1ResKdX5C1g14RoEAJd%2Bl5CUBgKW3%2BmpAH%2BDei38ICQpd9qlqODMg%2BIkE9Po8T8Ia%2BEudvtHkoOXTmdY%2BYV9bKeZl7KyKLfQH0UEREwpoLsB%2BQ6J7mq57N6EvXGQyre%2BUneWL2T5J2165nFa5%2BRnMus9Enq9I%2BYUdoFMQdQGaoyvYKRTw6Og2nP%2BTLyjGBfqv6wQGC6UaH%2BQOC6VKSoGZytehQ8wsOoYZUTFv9lFmhHp6j3L5iPwxoTYXa5sjIYIxOp%2FUNtarcPRIoMzSbHnf3EOW83TqOTddeVsqesmgC0%2B5y0WSCC4FVZTwfOrRAeiYTU%2BijJTjyW75HZISBE5lVR1MMC9EyDXGbw%2FXcFoEojvx06h2r%2FhslfdWcnd8mV8skRxfwchaq86SxKwUGSSDl5%2F8QXVB36jyczq4YqkhNZF8hoMV77pnPHuKUQ18A%2F9OpQcJTJqkIUe55CGQ6g1JNCb1eCp5cX%2FcywqMUtRzvPp%2FQvL75KbPoSg3ASvj2mpizfllVIr1vNlXD9%2FtwH6vV8WFtYqkjqtvXGBCq3HOQrfzgsKrc6W27muZ9OkBju3e7K07jDi3BTJH41X74lMoLLKbjYmwfqtGAWaPxuzVy76DXAeunPYM8nbO4RIUrPG6SC2TcYbzMDZjwxdF5%2Blj5LXC4LLtul99BsVEo0w7Grss2SNrTc93QJlV6pN97LaAQthrZbnNVieIzP5ULtRsLw4Uh0bFEZKCd4ZHDuLdGKxvd33P0KolJvvKP%2F3H3DiKwH6xBysrVGnFug4aqDmE4BD1wYolyvTxeIHfRTY5ym7Lxc5yEM%2BkbXEtD2p0epzZOAuNmdN1EY9FGOR1AvK7oBiWwceiljDCXNchPn5ns3WYRD806gEeqpBHksdPwXsk1ss9Ec29bsodGMRACFdLBx8p569rg4ch4eSBCy2%2FICTw6U0GDpW9v7sJCBCR1WmkJz3gbkGRgDC4inrMs3xxyA5Tamb9wbLNmrCWKkcKFIofgGYGbPSE8oXwOhW%2BPAlz82WDVBxv6waK6Ld6flBje0LlZDU6ybGvdW96mcAj7gibkcXyn1JhAsjC20dhMfIdJ9tdc%2Fj7hurOqRGR%2BZS2NSQGrftRO0eNZJY4iFdHPb32QBBXlVzH8BmZ%2FZN9PoUo6EExU%2FGzsU3iwTQQC0X%2BsXBz1fze1179MLHtMEFGIS3FuomRDDffHzXlMjS9smq4JDn9Hg%2B2aKPLLGq5%2B9q3I1%2BfEQ7hQoxYWpDanP3CZwhwxVQOCfs8qoa%2BO7VcLSVmIqoWlfjsvgAoGe2ImlcG47x2kN3mKEF2373BoT1Vbbg%2BRxkqsq261UeI7D1rl9B%2BBBErNGyCwYTG68nptjWOYdM%2F73R7wzqckwBrPe7ngM2g5mqZXKMkAZ3LwfqkUMpis2aiz9QeX7pGgjQqVxerJbYQ1uJpUvr32NYUVpwKTEp0rvjVnUnrkcqtWHroPccNeu%2BuQcX%2FZTqWeHzWfRz18VH4nYUC6R8%2BrqBZXh6eIZsocYsNS5ZN%2BljupeVZbsHr69R67IoyDuJ%2Fzd4FgYeZKU6091Zq29rpnhRTkAxoBcOubVKsTNdGYyTNbeJpYktXqCAS4mbtqnEpKGLzv8qUjUDnK7EchIjFypa9hZJ%2Fn1%2Fez23Mmri%2B08SI6E%2BhRvWEPPUSbOmsLbstTd2TQ1Uqpf62Rd4ZW2A%2FiSEeOXNjW8ODfYF%2BQAnkUhsTYTNqIratJq5O3Rs0faBOnc3jxW%2BfRTYN8PNJZvhe8E3jI6AL%2BFAFwrn%2FtOj3FPrT3y%2BAAjIxodHKIBZXowfP%2F1Kp8JAcZP5Qpx6EdU%2F5tUNue%2BwsC9McSckwffFXeGxmUtJUTIAZaUiNsqQ04SvL1rsRByHNcMx9MKXWj5BVzc8btEd70RWdvXL5tAEwIXVa4Zk8Krk52e7YfbGr4CKWfy56anoqd4%2BItYK7EGlakgfS4j%2BMbCYISbyi9UFUqr84QrEOGg2NlKp2%2FXiS8m2dzBZ6FTcDph6YjoMcSvQhGWK6tAE520KxfetSaj%2BeoJSQ6Bm6BAV5cxMu1cOZNy%2BI4%2BVgR24%2FP44bS7C9GchiZnvAuI5V3Aqir8LNfoyYj3dm8PLF6haura30dsgXwFHpxCr2i6aBIzlQ8PkFqg6wuoJZOkvQ%2B13cWS7N2Vlvd%2BUiU%2Fa0ZrDs1SrX6KKgaszEdYJsHkX9WhH%2FNUNOlCk448mBpBQRjtC68bL%2BIAZvUMB6PlA0L3PWVyCPGpEV8t97J5%2BX5V9460GVGAld7zeEVce4A1S3I69HDcJ2QBfP8dHsiWNVVhwu05gv%2FZ43tem2lVPeu5JQ4MK8KPuUvC46zVEIOUSNepPq4y525qx5iZWlQdOleb6vhbipo9GtP3KmMUrimEHDt2%2FYzKPhcN3lJGLOSYwpJVtToS76M0dP9QXy%2Fr1grOFBuD9EjPJx5W%2B6rebPRDY0w%2BWpZsBnk2GNkS%2FahSLuHNc1opY53z3KlLLPJA18Uq3xv%2B%2Bum62vQJGenydbUzlznTGouIpgJnzOL%2F5pp7PjBbIgDZigh0BEagVHcRGZc7mpm6mNsMBkxYlEtcAAldZZWhiXoQ238n3jTucHW1roV0wRW9W%2BDQsGRXQvBxWyZ43QlTGK8s4n1pv9kuCUHeIL4hytG8eKBz6DO0aYEjrfL8EQjls9Z5Obp6QvugR3fzV55MjiceAwe4j9qpOXP8X2g3ABUKlV2M3DzQ2cYxXVQYjcQ7z1jhRDDau9R0Qd3S%2FDXCJloM0%2FdQmjR6olHaBZxW51B9XGZWSF%2FMwHiz2wLJJDBrxRLNo3BYjzo35DdEsHDrPBz3Y90k2yJFXGcjzAJqoSYJCfUp9wz3SKses%2BjpxMNusUY92mtzWNHdAMpjuIXpMIZ4CknM8TeV2nhPMzrZjmlUy6RJ0LFB8IlLmaWVa2boBoDCK%2FDBooobHqvJMEoqr9bez7k%2F1Rc3LViTQVLb%2B6sAek%2BPaZnB83OXsNGGv6TuhFdRYEhdhJ4f%2B4Ohrfcy74mjmtF9AGs0sFw0gdt3mKPtlHSOm9QvYNbXt5muHN8TMBmOX7F9WQzu2rboWq2dX1ONYuD%2Bim7bqwvzgc7%2B5dY9%2BXv%2F0IYXsBYSv8BB6aRgXh9Tl8oEByTbi2InVbJAmVOOEb1zoKSLxYmQbKWECONZEFNKnV0Ez35ypOimO0k5btvF5DTgZ3VApHPJs9vgvy0aPt8aKq1g1JmN%2F%2FOcMPVRehWpqw0OdSbUp9Y6DavG%2F5thVLnCrDwQGiBAKZ2RDH%2Bk%2FYrxIxvDkfCTPwSNSNYLzMapizUG9Ybkl1OoiF9mg8moSQ93K9xb4U7iI0nY9kxsfnghXpTcT2CohwkFw2oYPuTMhr9zSXO3PyOZEk6CUO%2B6nxcYfXYx3RqnFTFifdej9x4t1i6y%2Fil9oilncP81r8HZY541RMmmUw0KrupmeSnkcUPpcxD0NEPi2KJpkZntrkOafAqIxzP0C4Ne9vVKnnkS7SwzymR21y1PDj1raeXt7Nw6nse2k3PpsgPLmmrdsg37FWoq%2F1YykTu1PNOLPcPN7dwH3RCjAVvwpOcmo2zPvYkK3IV000dEcnH1Q85o6v9RG4Xgm2N9pR3fBKEznD8sjV71VKYPuIVdWXSyCubT4y5%2FlT478gF%2FaKyTy85gF4oa%2FpyZQoPNfF5z4%2BIoVhNSgU%2F2c4KT44iY0GEqUV5xNrMIXBdcBjWzMinEhNy7v2wX9dXK53PdpohAI0bQEywaPDPQVC8xGh6TJTJrkAoN57U2Saj8n5DgcDLfhXHRo314uUtRssXdnlr40y9JP3VWPQ6M%2Bn2UXmBiKas6Evz%2F0JFCRv%2F0VXwpYDtipwRP3HsTa0T0Ip5CmY4aMr%2FKLS8Mb%2FmyV32k5JlPxhMiMVbKbnlsgd8nGn2N1tCwXIWCOJDh5Bt1xtOacEVaptVzVPsgRvcYMY9jNyMqfLf6Ec0LEWuYfqd1RsumiFEhflTitzbNqdOOsUekXhusKLoRjvED3qYpKTuxzgq2Brjyn6Jxu1l7z78C4hKryJAXubJkCLwLEoJdU63mqKtTfmzOSC7d2VaAR7rE4%2BV8S%2FI%2FGH%2FO6I5LFj9BVAbYLjUDvtw5Evniw19mpiqGlaTCWx9yItZU9XhEmHS9FWUlROB49DdA3212J2mTkVOQtK%2F947DwQ8w8tE81y1IY4bgQJx5TxkuKE0yuYWgBZ2znuqfF8iW7rkMvSgAQkccuv0keMFhSe4%2BMQIxJrSgHgVWC9%2B0XlVa49i8z46QvGiB3kQDTs9FIvtRx%2B2dzhXB7LWFeGi44q%2BWXEVkgSS6aLmzYcAOAz5f5bnV8yEvsKeQnlA7uauSajTlzTVR6VisB6q1Y7tMAIX7WBmrHZZkl2upmo06J4EtuSdQ%2BUhZ%2Bks44v7DVqfcRZQJLUIgP%2F6ksiPrqdN8tvYBAk0Z9DLy3WTpbXRQtQbk4vyR5h%2F%2BpU2sq1unIOdyBwrBqvkvA55%2FTOdDJxuHHlh0nBqovoe5Av%2Bgemyx%2FIMJu9G2xK0citY01D2qucaHcWIB6HM%3D&__VIEWSTATEGENERATOR=2A35A6B2&__VIEWSTATEENCRYPTED=&__EVENTVALIDATION=oXhvJgiROKfjPCOXqM9dszrfA76wxQ3HtygSyn4MXhQgiAE6xB8%2BV%2Ba8nsT5c7BL89r1lO278y7p13Pky2zNQjM2e%2BRj66rl7ZgAmVt7LAWPokU%2BhBWS9h9gmZQVulgHn5Y3B84t7LqvUFuiudp44Hhe9UfXr1cZdVbMo5KZ%2BVGB2FJ7tukoItbKtGXKvYLWqayEvrdvdYUfDhyhfpxBL4hvRXHrQjUg%2Ff5C9DgonNiZZbUsQoGNK3wUWzpuAJ8Xra9PGw%3D%3D&tep_name=&organ_name=&site_address=&ddlPageCount=@count@&'
    mybody='scriptManager2=updatepanel2|AspNetPager1&__EVENTTARGET=AspNetPager1&__EVENTARGUMENT=@num@&__LASTFOCUS=&__VIEWSTATE=Pqcz4t79IaPiSkOD0Xp3W5j2%2BbxSn1tAZVR71ynNt84Rg2YF93Mn9G41%2BFq5CoM6o1oI3ysiWpZF9fB%2BPs9mDTvRsO4R7jx8HysaJ4drUTg%2BBTyb7HjJ8w0JzqMoRpfar7P2DVgMEzr%2BP%2FBBDgb%2BqIjqaGZF3vMCk%2FVjeT%2B8iDxRjHSpqrx8m%2FrwmxePFTM8FzGOg9UUvUHLfmiS2A%2FV%2BXoDJWu0avWt5b1iBgFTvH2D1EadegFHs01I9xtEuoey9bmDaa%2BmavSblEyWThgFE4hIZlZ2REIZ710qkAFsb0DbUCnQKkU%2FyM6kb%2FwnUz0cRnyyylf9inFHHoBYyqnMvaBjNKNG3hWNfxJptWsRP2B05c9PZ88ShA56O5u3Tx6jkctcqeZnb6YS3gqnKfwNFqY7DVSK9bH7J%2BdkOZEheandI8S6Vvqlh3wTt3Gv33cokiqYxNAOLPQ7RrZOoq2VvNzvojjpRWQ%2BogtKoZYyD3agqa3rw%2BH2KNaagkRacqgkBBqDw049HBk0e%2FrCDUwjn2c%2FRIOuimJJFm3dpu3pDM2fNg1dyuIyv7Z969w4AiYeS7OnvCLD%2BZdUxVU3Z9AwheO53%2FjSUqUOakEavaaj9LuF4fOIpPtWzwRlxk%2BGm78OK1RuVUt%2BB%2BsQCcc6zD1LceUyDlm98g8i4qE20KpV9XGUI7eMvNdPIN2TO%2BHIM89nVxkA1Jpw8cXAvsDAgXN5XHsU1tS%2B0R57G32d%2B51qA3dU3zELcyxQfkYMRZZBCpMIbCzIeoJoO4qb%2BPH273YrJ%2FqR%2B5Z%2FrckBMUv8sGMDmh5yWOshRm6MRpmf9tEP0YH%2FLd43bKGk9GU9hyMdU4HXVUj%2FYbiKkD1iI%2FC1ov3SpoYUDyQAk3ygYV9d8kj%2B%2B8TiFSQp03tAAagDoKlQjLXVOxlP9GVVJscDRdxrcrCdwm3QarmfuW2WoKKOjiXVpPGkaW2x8b2abSf20qVUbt%2FjB7v1feMMxjXIkFB1wcbvWExKXHIhlyBz%2F%2FcgwQLp7fL1yr4QCmTlsexRvHzIkKLpcXaR%2B5n0dg8cFngWTh7Mnfqa3mnIofUDabtSmjtfBnpupIsGQvfD1TdNWc0dmHgwUvQBAXRAFog4JgJumuiHRohMeMcKoEQQexQ9uPPy%2BYTJwIp6Y6C5AUCbt3AsKxXmJbCsU%2BWpdc8AONlGc8eDKHR6yZXzph7nFiABHrjM3BiEHxjru5bqSbXb9NWkxqKMJ4Y5%2Ftzy2g22V0e%2BNrg4%2BAbhaihmLrYGP%2BWm37DbMRagfQwQR4FsLg7gED1chdRha1u6DHkAyiMuihRuWmd6y9Z4stWRLRRQfJxX9jb6%2FsTHuXCEn3kYY1rI%2Feh011stqbbBagwMqu54hll%2BMoBnroAgiWuFMuICc9qhwkf7mLnPsY1QQHYlxurpBFp%2F7h5AKaNsNbG8HslIdqCHs%2F%2BtQcoYSNqpQiM9jtbqiAXl9W3vQfP5z6UXbjHB%2BsOcLAodawXX3tASRlw5rFOBwgAS9EGjiDy1BAtm%2F8i8OODVBKbg5BWEQSKjKZsHpElsfKgw5u3ih6aU5I5BLiWG%2Fvo4O2WrikoE87Rnu8Cog9S5cy7eGKODQgt5RSf%2F6I15r9Sd9WvPAHK4mu4rLYAqYz3%2FkzY3TxxNRJXI3NA%2FuddEdouEzBTH3Hz8jREFDBrf1riXqEnj2Zfs5NdhRv%2BmZmiDjBvibJ0ryT%2BPH08%2BCOR5zpLmrDsZCkhBnyoUEX3N%2BoQDoI25Hr0J4OXIVfM5lEwEJA41IkdMsAPzQZSOf9VF%2FafnwN9BNEQPw5yjnkqr4SxfTvyGGWJXM1Nz1i3M02dZsp90IoHPA%2Fpqx%2F%2Br%2F2BHW9JiNUmnAW4e2myo8vOkYNJNKAp0slxFUMwxUeWPtyv4C%2BvUCZVeh6nUa%2BKzCC3sBAcYcFxDNW4nO8PUKP6U7mJsSK4t4keE66DxpDJgJfkvQfeEjg22MwEBXsWhOB4O5NczRigDRUgMJ%2BNXd22NH%2F2dYWvMAWq2YPoj3p07HWXlYVgKmx%2FX2QmSNBi%2B%2FC3m2fSqsZc2YN1kjEAxdMiLX0KoMpxWkAdxi9zA4iM7uuzDoCKWj%2F5yoBzKsHp1MCRfrZMEnLQiQ5jadty3lDw2ExBoyDnwrbgbyflUCDRyC0ydy9JyCgBP8ZLwspf5lr8O7G9y8IcKjHlykGjPjpJJNZiG7fley4s0kxBUmrU55pWwBYikXezJQAb61CuUb6BdO0Fb281KZWKp0p51wHvj2f7EbKWrbCKK60Hjs7Is608pU3yWU09LtkSbKtUvxbz115LHuE8fZLN91nxfp%2BEa5XlrUIwXw4U0y2hWm81xK6GdnyzzF0IDW2D8FQCjsS5t4KmGBHK3Cghlt%2BEN16uMwdjKywD2YaQAI14R3H6Am8t2%2F45HKlBAjVVtV4Gq9f7rhag%2Fl%2FmMiQr6jKw%2Bq3PP5yxQnm7Qbim5%2BOdTFgDYTZ%2Be9X85ARr3jb23%2FxFgeNYa2bEPX3nSAD%2BjAPNjNfMxPjb4KxvDb85BhhHAjokfFrO3u5JO40mipjo5mA4DHOArNc%2Bo9iRQGg%3D%3D&__VIEWSTATEGENERATOR=2A35A6B2&__VIEWSTATEENCRYPTED=&__EVENTVALIDATION=ucWgk%2BqCEaHTULXG7DBqxviN%2F7Q1g7Gysb%2FzeaoavXVzJPwwGbdgrtaqW4E2sZILFZm9R5lAUk2x1GV43P7TdzTUE%2BEFigF5HCgSfGFBz2JY8SaBDA2BiQTl%2BeIGzUU8bbI%2FZNr6HH9kcMs7dfsgW54PPdhpJ293PWwFTfIVNoGhzkWPdZzaSLANZqF8izRNGlCSUBrgip5TCVMVU5OAvl6nL1BWnIBLv4OY8RsCVPA%2FWdZD1nFkwWXWPAot4raePrc1AQ%3D%3D&tep_name=&organ_name=&site_address=&ddlPageCount=@count@&'
    mybody=mybody.replace('@num@','1')
    mybody = mybody.replace('@count@', '10')
    r0 = requests.post('http://zjj.sz.gov.cn/ris/bol/szfdc/index.aspx',mybody,headers=headers)
    #r0 = requests.get('http://zjj.sz.gov.cn/ris/bol/szfdc/index.aspx')
    ori = r0.text
    print(ori)
    ori1=ori.replace('\r\n','').replace(' ','')
    cernames=re.findall("'target='_self'>(.+?)</a></td>",ori1)
    #print(cernames)

    regex ='">(.+?)</tr>'
    list=re.findall(regex,ori1)
    for str in list:
        desc = re.findall('<td>(.*?)</td>', str)
        if len(desc)<=5:
            continue
        num=desc[0]
        cer_ori = desc[1]
        cert_name = cer_ori.split(">")[1].split("<")[0]
        href = desc[2]
        path=href.split("<ahref='")[1].split("'target='_self'>")[0]
        name = href.split("target='_self'>")[1].split("</a>")[0]
        producer=desc[3]
        block = desc[4]
        time=desc[5]
        global index
        global base_res
        index = index + 1
        firstData="{},{},{},{},{},{}".format(index,cert_name,name,producer,block,time)
       #这验证数据是否存在如果存在则不再新增数据 isok=Fasle 则默认是新数据
        #isok = isExistBaseData('house_base', cert_name)
        isok=False
        if(isok==True):
            print('已经存在数据',cert_name)
            continue
        else:
            print('新数据',cert_name)
            #if(index==1):
            getProduceData(firstData,path,cert_name,name)
        #index+path+name+producer+block+time

        #print(index, path, name, producer, block, time)
       # if(index=='1'):

        #print(href,'=',name,'=',path)

    #print(ori)
#step2 获取第二部数据
def getProduceData(firstData,path,cert_name,project_name):
    print('获取第二部分数据','getProduceData')
    r0 = requests.get('http://zjj.sz.gov.cn/ris/bol/szfdc/'+path)
    ori = r0.text.replace('\r\n','').replace(' ','')
    #print(ori)
    location=re.findall('宗地位置</div></td><tdcolspan="5">(.+?)</td>',ori)[0]
    date = re.findall('受让日期</div></td><td>(.+?)</td>', ori)[0]
    district = re.findall('所在区域</div></td><tdcolspan="3">(.+?)</td>', ori)[0]
    year = re.findall('使用年限</div></td><tdcolspan="3"><divalign="left">(.+?)<br/>', ori)[0]
    use = re.findall('房屋用途</div></td><tdcolspan="5">(.+?)</td>', ori)[0]
    precount=re.findall('预售总套数</div></td><td>(.+?)</td>', ori)[0]
    prespace=re.findall('预售总面积</div></td><tdcolspan="3">(.+?)</td>', ori)[0]
    global base_res
    secondData="{},{},{},{},{},{},{},{}".format(firstData,location, date,district,year,use,precount,prespace)
    print(secondData)
    base_res= base_res + secondData + '\n'
    #print(secondData)
    regex = "ahref='(.+?)'target"
    list = re.findall(regex, ori)
    path_list=[]
    for str in  list:
        if 'building.aspx' in str:
            path_list.append(str)
    #print(path_list)
            getSuiteData(cert_name,project_name,str)

    #print(ori)
def getSuiteData(cert_name,project_name,path):
    #print('getSuiteData',path)
    r0 = requests.get('http://zjj.sz.gov.cn/ris/bol/szfdc/' + path)
    ori1 = r0.text.replace('\r\n','').replace(' ','')

    regex = "housedetail.aspx\?id=(.+?)'"
    list = re.findall(regex, ori1)
    #print(list)
    for str in list :
        houseList.append(str)
        certList.append(cert_name)

def getDescData(certname,id):
    #print("getDescData",certname,id)
    global desc_index
    desc_index=desc_index+1
    r0 = requests.get('http://zjj.sz.gov.cn/ris/bol/szfdc/housedetail.aspx?id='+id)
    ori = r0.text.replace('\r\n','').replace(' ','')
    #print(ori)
    name=re.findall("'target='_self'title='*.*'>(.+?)</a>&nbsp;>&nbsp;<ahref='", ori)[0]

    pjname=re.findall("id=*.*'target='_self'title='*.*'>(.+?)</a>&nbsp;>&nbsp;<ahref='",ori)[0]
    building =re.findall('项目楼栋情况</td><td>(.+?)&nbsp;</td>', ori)[0]
    price =re.findall('拟售价格</td><tdcolspan="3">(.+?)</td></tr><tr><tdalign', ori)[0].replace('元/平方米(按建筑面积计)','')
    floor = re.findall('楼层</td><tdalign="center">(.+?)&nbsp;</td>', ori)[0]
    number = re.findall('房号</td><tdalign="center">(.+?)&nbsp;</td>', ori)[0]
    use=re.findall('用途</td><td>(.+?)&nbsp;<', ori)[0]
    total_space=re.findall('建筑面积</td><td>(.+?)</td>', ori)[0].replace('平方米','')
    use_space=re.findall('户内面积</td><td>(.+?)</td>', ori)[0].replace('平方米','')
    share_space = re.findall('分摊面积</td><td>(.+?)</td>', ori)[0].replace('平方米','')
    global  desc_res
    descdata="{},{},{},{},{},{},{},{},{},{}".format(certname,pjname,building,price,floor,number,use,total_space,use_space,share_space)
    desc_res=desc_res+descdata+'\n'
    if(desc_index%100==1):
        data="{}={}".format(desc_index,descdata)
        print(data)
    #saveData(res)
    #print(str)
    #print(ori)
def saveBaseCSV():
    print('saveBaseData')
    global base_res
    data=base_res
    fh = open('d:/data/house_base.csv', 'w', encoding='utf-8')
    fh.write(data)
    fh.close()

def saveDescCSV():
    print('saveDescData')
    global desc_res
    data=desc_res
    fh = open('d:/data/house_desc.csv', 'w', encoding='utf-8')
    fh.write(data)
    fh.close()
def main():
    getFirstData()
    saveBaseCSV()
    savetoBaseDB()
    #for str in houseList:
    #    getDescData(str)
    #saveDescData()
    #print(houseList)

def main2():

    getFirstData()
    saveBaseCSV()
    savetoBaseDB()

    i=-1
    for str in houseList:
        i=i+1
        getDescData(certList[i],str)
    saveDescCSV()
    savetoDescDB()
    #print(houseList)

def savetoBaseDB():
    '''
    print('开始查询数据表')
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select distinct cert_name from  house_base'
    cursor.execute(sql)
    dates = cursor.fetchall()
    db.commit()
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    '''
    dates=[]
    print('开始插入数据')
    path = 'd:/data/house_base.csv'
    df = pd.read_csv(path)
    listdata=[]
    if(len(dates)==0):
        for ss in dates:
          listdata.append(ss[0])
    df = df[~df.预售证号.isin (listdata)]
    print( len(df))
    df['cert_name'] = df['预售证号']
    df['project_name'] = df['项目名称']
    df['produce'] = df['开发商']
    df['location'] = df['所在区']
    df['approval_date'] = df['批准时间']
    df['address'] = df['位置']
    df['use_time'] = df['使用年限']
    df['use_way'] = df['房屋用途']
    df['precount'] = df['预售数量']
    df['allspace'] = df['总建筑面积']
    dbs = df[['cert_name','project_name', 'produce', 'location', 'approval_date','address','use_time', 'use_way', 'precount', 'allspace']]
    #df = df.set_index('project_name')
    #engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    #dbs.to_sql(name='house_base', con=engine, if_exists='append', index=False)

def savetoDescDB():
    '''
    print('开始清空数据表desc')
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select distinct cert_name from  house_desc'
    cursor.execute(sql)
    dates = cursor.fetchall()
    db.commit()
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    '''
    dates=[]
    print('开始插入数据')
    path = 'd:/data/house_desc.csv'
    df = pd.read_csv(path)
    listdata = []
    if (len(dates) == 0):
        for ss in dates:
            listdata.append(ss[0])
    df = df[~df.预售证号.isin(listdata)]

    df.loc[(df.售价 =='--'),'售价'] = '0'
    #df=df[df.售价!='--']
    df['cert_name'] = df['预售证号']
    df['project_name'] = df['项目名称']
    df['building'] = df['楼栋']
    df['unit_price'] = df['售价']
    df['floor'] = df['楼层']
    df['house_number'] = df['房号']
    df['house_use'] = df['用途']
    df['all_space'] = df['建筑面积']
    df['use_space'] = df['户内面积']
    df['share_space'] = df['分摊面积']

    dbs = df[['cert_name','project_name', 'building', 'unit_price', 'floor','house_number','house_use', 'all_space', 'use_space', 'share_space']]
    #engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    #dbs.to_sql(name='house_desc', con=engine, if_exists='append', index=False)

def debug():
    # 添加请求头
    headers = {
        'Content-Type': 'application/x-www-form-urlencoded'
    }
    body='scriptManager2=updatepanel2|AspNetPager1&__EVENTTARGET=AspNetPager1&__EVENTARGUMENT=@num@&__LASTFOCUS=&__VIEWSTATE=CRLk4GeAnUzjmsIrkr1AQSMf9%2B%2B0VHmINHEqQMsRQOz7Jiv4Ww22eiEgP8AnNthVvjlKwHrCzhlHK2NeJX4OlqCqto1Jj%2Bl9imgVCqUA31wGaC6NoLblcCDFggQj8Pph1ncaOTuCjOThyRUScD3Rn9GBGiLk%2BvAAav1ResKdX5C1g14RoEAJd%2Bl5CUBgKW3%2BmpAH%2BDei38ICQpd9qlqODMg%2BIkE9Po8T8Ia%2BEudvtHkoOXTmdY%2BYV9bKeZl7KyKLfQH0UEREwpoLsB%2BQ6J7mq57N6EvXGQyre%2BUneWL2T5J2165nFa5%2BRnMus9Enq9I%2BYUdoFMQdQGaoyvYKRTw6Og2nP%2BTLyjGBfqv6wQGC6UaH%2BQOC6VKSoGZytehQ8wsOoYZUTFv9lFmhHp6j3L5iPwxoTYXa5sjIYIxOp%2FUNtarcPRIoMzSbHnf3EOW83TqOTddeVsqesmgC0%2B5y0WSCC4FVZTwfOrRAeiYTU%2BijJTjyW75HZISBE5lVR1MMC9EyDXGbw%2FXcFoEojvx06h2r%2FhslfdWcnd8mV8skRxfwchaq86SxKwUGSSDl5%2F8QXVB36jyczq4YqkhNZF8hoMV77pnPHuKUQ18A%2F9OpQcJTJqkIUe55CGQ6g1JNCb1eCp5cX%2FcywqMUtRzvPp%2FQvL75KbPoSg3ASvj2mpizfllVIr1vNlXD9%2FtwH6vV8WFtYqkjqtvXGBCq3HOQrfzgsKrc6W27muZ9OkBju3e7K07jDi3BTJH41X74lMoLLKbjYmwfqtGAWaPxuzVy76DXAeunPYM8nbO4RIUrPG6SC2TcYbzMDZjwxdF5%2Blj5LXC4LLtul99BsVEo0w7Grss2SNrTc93QJlV6pN97LaAQthrZbnNVieIzP5ULtRsLw4Uh0bFEZKCd4ZHDuLdGKxvd33P0KolJvvKP%2F3H3DiKwH6xBysrVGnFug4aqDmE4BD1wYolyvTxeIHfRTY5ym7Lxc5yEM%2BkbXEtD2p0epzZOAuNmdN1EY9FGOR1AvK7oBiWwceiljDCXNchPn5ns3WYRD806gEeqpBHksdPwXsk1ss9Ec29bsodGMRACFdLBx8p569rg4ch4eSBCy2%2FICTw6U0GDpW9v7sJCBCR1WmkJz3gbkGRgDC4inrMs3xxyA5Tamb9wbLNmrCWKkcKFIofgGYGbPSE8oXwOhW%2BPAlz82WDVBxv6waK6Ld6flBje0LlZDU6ybGvdW96mcAj7gibkcXyn1JhAsjC20dhMfIdJ9tdc%2Fj7hurOqRGR%2BZS2NSQGrftRO0eNZJY4iFdHPb32QBBXlVzH8BmZ%2FZN9PoUo6EExU%2FGzsU3iwTQQC0X%2BsXBz1fze1179MLHtMEFGIS3FuomRDDffHzXlMjS9smq4JDn9Hg%2B2aKPLLGq5%2B9q3I1%2BfEQ7hQoxYWpDanP3CZwhwxVQOCfs8qoa%2BO7VcLSVmIqoWlfjsvgAoGe2ImlcG47x2kN3mKEF2373BoT1Vbbg%2BRxkqsq261UeI7D1rl9B%2BBBErNGyCwYTG68nptjWOYdM%2F73R7wzqckwBrPe7ngM2g5mqZXKMkAZ3LwfqkUMpis2aiz9QeX7pGgjQqVxerJbYQ1uJpUvr32NYUVpwKTEp0rvjVnUnrkcqtWHroPccNeu%2BuQcX%2FZTqWeHzWfRz18VH4nYUC6R8%2BrqBZXh6eIZsocYsNS5ZN%2BljupeVZbsHr69R67IoyDuJ%2Fzd4FgYeZKU6091Zq29rpnhRTkAxoBcOubVKsTNdGYyTNbeJpYktXqCAS4mbtqnEpKGLzv8qUjUDnK7EchIjFypa9hZJ%2Fn1%2Fez23Mmri%2B08SI6E%2BhRvWEPPUSbOmsLbstTd2TQ1Uqpf62Rd4ZW2A%2FiSEeOXNjW8ODfYF%2BQAnkUhsTYTNqIratJq5O3Rs0faBOnc3jxW%2BfRTYN8PNJZvhe8E3jI6AL%2BFAFwrn%2FtOj3FPrT3y%2BAAjIxodHKIBZXowfP%2F1Kp8JAcZP5Qpx6EdU%2F5tUNue%2BwsC9McSckwffFXeGxmUtJUTIAZaUiNsqQ04SvL1rsRByHNcMx9MKXWj5BVzc8btEd70RWdvXL5tAEwIXVa4Zk8Krk52e7YfbGr4CKWfy56anoqd4%2BItYK7EGlakgfS4j%2BMbCYISbyi9UFUqr84QrEOGg2NlKp2%2FXiS8m2dzBZ6FTcDph6YjoMcSvQhGWK6tAE520KxfetSaj%2BeoJSQ6Bm6BAV5cxMu1cOZNy%2BI4%2BVgR24%2FP44bS7C9GchiZnvAuI5V3Aqir8LNfoyYj3dm8PLF6haura30dsgXwFHpxCr2i6aBIzlQ8PkFqg6wuoJZOkvQ%2B13cWS7N2Vlvd%2BUiU%2Fa0ZrDs1SrX6KKgaszEdYJsHkX9WhH%2FNUNOlCk448mBpBQRjtC68bL%2BIAZvUMB6PlA0L3PWVyCPGpEV8t97J5%2BX5V9460GVGAld7zeEVce4A1S3I69HDcJ2QBfP8dHsiWNVVhwu05gv%2FZ43tem2lVPeu5JQ4MK8KPuUvC46zVEIOUSNepPq4y525qx5iZWlQdOleb6vhbipo9GtP3KmMUrimEHDt2%2FYzKPhcN3lJGLOSYwpJVtToS76M0dP9QXy%2Fr1grOFBuD9EjPJx5W%2B6rebPRDY0w%2BWpZsBnk2GNkS%2FahSLuHNc1opY53z3KlLLPJA18Uq3xv%2B%2Bum62vQJGenydbUzlznTGouIpgJnzOL%2F5pp7PjBbIgDZigh0BEagVHcRGZc7mpm6mNsMBkxYlEtcAAldZZWhiXoQ238n3jTucHW1roV0wRW9W%2BDQsGRXQvBxWyZ43QlTGK8s4n1pv9kuCUHeIL4hytG8eKBz6DO0aYEjrfL8EQjls9Z5Obp6QvugR3fzV55MjiceAwe4j9qpOXP8X2g3ABUKlV2M3DzQ2cYxXVQYjcQ7z1jhRDDau9R0Qd3S%2FDXCJloM0%2FdQmjR6olHaBZxW51B9XGZWSF%2FMwHiz2wLJJDBrxRLNo3BYjzo35DdEsHDrPBz3Y90k2yJFXGcjzAJqoSYJCfUp9wz3SKses%2BjpxMNusUY92mtzWNHdAMpjuIXpMIZ4CknM8TeV2nhPMzrZjmlUy6RJ0LFB8IlLmaWVa2boBoDCK%2FDBooobHqvJMEoqr9bez7k%2F1Rc3LViTQVLb%2B6sAek%2BPaZnB83OXsNGGv6TuhFdRYEhdhJ4f%2B4Ohrfcy74mjmtF9AGs0sFw0gdt3mKPtlHSOm9QvYNbXt5muHN8TMBmOX7F9WQzu2rboWq2dX1ONYuD%2Bim7bqwvzgc7%2B5dY9%2BXv%2F0IYXsBYSv8BB6aRgXh9Tl8oEByTbi2InVbJAmVOOEb1zoKSLxYmQbKWECONZEFNKnV0Ez35ypOimO0k5btvF5DTgZ3VApHPJs9vgvy0aPt8aKq1g1JmN%2F%2FOcMPVRehWpqw0OdSbUp9Y6DavG%2F5thVLnCrDwQGiBAKZ2RDH%2Bk%2FYrxIxvDkfCTPwSNSNYLzMapizUG9Ybkl1OoiF9mg8moSQ93K9xb4U7iI0nY9kxsfnghXpTcT2CohwkFw2oYPuTMhr9zSXO3PyOZEk6CUO%2B6nxcYfXYx3RqnFTFifdej9x4t1i6y%2Fil9oilncP81r8HZY541RMmmUw0KrupmeSnkcUPpcxD0NEPi2KJpkZntrkOafAqIxzP0C4Ne9vVKnnkS7SwzymR21y1PDj1raeXt7Nw6nse2k3PpsgPLmmrdsg37FWoq%2F1YykTu1PNOLPcPN7dwH3RCjAVvwpOcmo2zPvYkK3IV000dEcnH1Q85o6v9RG4Xgm2N9pR3fBKEznD8sjV71VKYPuIVdWXSyCubT4y5%2FlT478gF%2FaKyTy85gF4oa%2FpyZQoPNfF5z4%2BIoVhNSgU%2F2c4KT44iY0GEqUV5xNrMIXBdcBjWzMinEhNy7v2wX9dXK53PdpohAI0bQEywaPDPQVC8xGh6TJTJrkAoN57U2Saj8n5DgcDLfhXHRo314uUtRssXdnlr40y9JP3VWPQ6M%2Bn2UXmBiKas6Evz%2F0JFCRv%2F0VXwpYDtipwRP3HsTa0T0Ip5CmY4aMr%2FKLS8Mb%2FmyV32k5JlPxhMiMVbKbnlsgd8nGn2N1tCwXIWCOJDh5Bt1xtOacEVaptVzVPsgRvcYMY9jNyMqfLf6Ec0LEWuYfqd1RsumiFEhflTitzbNqdOOsUekXhusKLoRjvED3qYpKTuxzgq2Brjyn6Jxu1l7z78C4hKryJAXubJkCLwLEoJdU63mqKtTfmzOSC7d2VaAR7rE4%2BV8S%2FI%2FGH%2FO6I5LFj9BVAbYLjUDvtw5Evniw19mpiqGlaTCWx9yItZU9XhEmHS9FWUlROB49DdA3212J2mTkVOQtK%2F947DwQ8w8tE81y1IY4bgQJx5TxkuKE0yuYWgBZ2znuqfF8iW7rkMvSgAQkccuv0keMFhSe4%2BMQIxJrSgHgVWC9%2B0XlVa49i8z46QvGiB3kQDTs9FIvtRx%2B2dzhXB7LWFeGi44q%2BWXEVkgSS6aLmzYcAOAz5f5bnV8yEvsKeQnlA7uauSajTlzTVR6VisB6q1Y7tMAIX7WBmrHZZkl2upmo06J4EtuSdQ%2BUhZ%2Bks44v7DVqfcRZQJLUIgP%2F6ksiPrqdN8tvYBAk0Z9DLy3WTpbXRQtQbk4vyR5h%2F%2BpU2sq1unIOdyBwrBqvkvA55%2FTOdDJxuHHlh0nBqovoe5Av%2Bgemyx%2FIMJu9G2xK0citY01D2qucaHcWIB6HM%3D&__VIEWSTATEGENERATOR=2A35A6B2&__VIEWSTATEENCRYPTED=&__EVENTVALIDATION=oXhvJgiROKfjPCOXqM9dszrfA76wxQ3HtygSyn4MXhQgiAE6xB8%2BV%2Ba8nsT5c7BL89r1lO278y7p13Pky2zNQjM2e%2BRj66rl7ZgAmVt7LAWPokU%2BhBWS9h9gmZQVulgHn5Y3B84t7LqvUFuiudp44Hhe9UfXr1cZdVbMo5KZ%2BVGB2FJ7tukoItbKtGXKvYLWqayEvrdvdYUfDhyhfpxBL4hvRXHrQjUg%2Ff5C9DgonNiZZbUsQoGNK3wUWzpuAJ8Xra9PGw%3D%3D&tep_name=&organ_name=&site_address=&ddlPageCount=@count@&'
    body=body.replace('@num@',str(1))
    body = body.replace('@count@', str(10))
    r0 = requests.post('http://zjj.sz.gov.cn/ris/bol/szfdc/index.aspx',body,headers=headers)
    print(r0.text)
def isExistBaseData(table_name,cert_name):
    #print('开始查询数据表base')
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select count(*) from  '+table_name+' where  cert_name =%s'
    cursor.execute(sql,cert_name)
    dates = cursor.fetchone()
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    if (dates[0] == None or dates[0] == 0):
       return False
    else:
        return True
def saveData():
    #savetoBaseDB()
    savetoDescDB()
#main()
if __name__ == "__main__":
    main2()